import pandas as pd
import numpy as np
# qiche/data/submit_example.csv
# qiche/data/evaluation_public.csv


# B = pd.read_csv('data/submit_example.csv', header=0)

B = pd.read_csv('data/Train/train_sales_data.csv', header=0)

M = {}
k = 0
for i in range(len(B)):
    if (B.loc[i, 'province'], B.loc[i, 'model']) not in M:
        M[(B.loc[i, 'province'], B.loc[i, 'model'])] = B.loc[i, 'adcode']
        k += 1


df = pd.DataFrame.from_dict(M, orient='index').reset_index()
df.columns = ['index', 'adcode']
# df.rename(columns={'$0': 'adcode'}, inplace=True)
df.insert(1, 'province', '')
df.insert(2, 'model', '')
df['province'] = df['index'].apply(lambda x: x[0])
df['model'] = df['index'].apply(lambda x: x[1])
df.drop('index', axis=1, inplace=True)


df = df.sort_values(['adcode', 'model'], ascending=[True, True])
df.drop('adcode', axis=1, inplace=True)

k = 2
for year in ['2016', '2017']:
    for month in range(1, 13, 1):
        if month<10:
            colname = year + '0' + str(month)
        else:
            colname = year + str(month)
        df.insert(k, colname, 0)
        k += 1


df.set_index(['province', 'model'], inplace=True)

# df.loc[('上海', '02aab221aabc03b9'), '201701']

for i in range(len(B)):
    year = B.loc[i, 'regYear']
    month = B.loc[i, 'regMonth']
    monstr = str(month) if month>9 else '0'+str(month)
    colname = str(year) + monstr
    df.loc[(B.loc[i, 'province'], B.loc[i, 'model']), colname] = B.loc[i, 'salesVolume']

df1 = df.reset_index()

df1.to_csv('data/sale_all_years.csv', index=True, header=True)